using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Windows.Forms;

    class orcConnection
    {
        static private OracleConnection orcCon;
        private static OracleCommand cmd;
        private static OracleDataAdapter da;
        private static OracleCommandBuilder cb;
        private static DataSet ds;
        private static string sql;
        private static string msg="";

        public static string Connect(string strDataSource, string strUserId, string strPassword)
        {
            string strConResult = "";
            
            string orcConString = "Data Source=" + strDataSource + ";User Id=" + strUserId + ";Password=" + strPassword + ";";
            orcCon = new OracleConnection(orcConString);

            try
            {
                orcCon.Open();
            }
            catch (OracleException orcEx)
            {
                strConResult = "An error occured: " + orcEx.Message;
            }
            // orcCon.Close(); ///15feb2012
            return strConResult;
            
        }

        public static DataTable GetUserTables()
        {
            sql = "SELECT TABLE_NAME FROM USER_TABLES";
            cmd = new OracleCommand(sql, orcCon);
            cmd.CommandType = CommandType.Text;
            da = new OracleDataAdapter(cmd);
            cb = new OracleCommandBuilder(da);
            ds = new DataSet();

            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable GetObjectStructure(string strObject)
        {
            sql = "SELECT COLUMN_NAME, DATA_TYPE, NULLABLE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE FROM user_tab_columns where table_name='" + strObject + "'";
            cmd = new OracleCommand(sql, orcCon);
            cmd.CommandType = CommandType.Text;
            da = new OracleDataAdapter(cmd);
            cb = new OracleCommandBuilder(da);
            ds = new DataSet();

            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable ExecuteSql(string strSql)
        {
            try
            {
                sql = strSql;
                cmd = new OracleCommand(sql, orcCon);
                cmd.CommandType = CommandType.Text;
                int effectedRows = cmd.ExecuteNonQuery();

                if (effectedRows == -1)
                {
                    da = new OracleDataAdapter(cmd);
                    cb = new OracleCommandBuilder(da);
                    ds = new DataSet();

                    da.Fill(ds);
                }
                else
                {
                    msg = "Query was successful";
                }
            }

            catch (OracleException orcEx)
            {
                MessageBox.Show ("An Error Occured: \n" + orcEx.Message);
            }

            return ds.Tables[0];
        }

        public static DataTable ExecuteQry(string strSql)
        {
            sql = strSql;
            cmd = new OracleCommand(sql, orcCon);
            cmd.CommandType = CommandType.Text;
            da = new OracleDataAdapter(cmd);
            cb = new OracleCommandBuilder(da);
            ds = new DataSet();

            da.Fill(ds);
            return ds.Tables[0];
        }

        public static string ExQuery()
        {
            return msg;
        }

        public static DataTable GetObjectData(string strObject)
        {
            sql = "SELECT * FROM " + strObject ;
            cmd = new OracleCommand(sql, orcCon);
            cmd.CommandType = CommandType.Text;
            da = new OracleDataAdapter(cmd);
            cb = new OracleCommandBuilder(da);
            ds = new DataSet();

            da.Fill(ds);
            return ds.Tables[0];
        }

        public static DataTable GetUserViews()
        {
            sql = "SELECT VIEW_NAME FROM USER_VIEWS";
            cmd = new OracleCommand(sql, orcCon);
            cmd.CommandType = CommandType.Text;
            da = new OracleDataAdapter(cmd);
            cb = new OracleCommandBuilder(da);
            ds = new DataSet();

            da.Fill(ds);
            return ds.Tables[0];
        }

    }
